**************************************************
*  do file to merge from individual spreadsheets
*
*  spreadsheets are stored in subfolders for each state\
*
**************************************************

version 13

clear all

set more off

**************************************************
*
*  the following statement should be changed to
*  to point to the base folder which contains the
*  the state-level subfolders
*
***************************************************

cd "D:\Research\Judicial Elections\"

* ALABAMA
import excel "Alabama\AL Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=1
gen stateid=1
* gen electid=stateid*1000+_n
save "Alabama\AL-Judicial.dta",replace

* ARIZONA
import excel "Arizona\AZ Judicial.xlsx", sheet("summary") firstrow clear
drop dubois
strrec District ("BLANK" "blank"=""),replace
gen type=2
gen stateid=3
* gen electid=stateid*1000+_n
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
save "Arizona\AZ-Judicial.dta",replace

* ARKANSAS
import excel "Arkansas\AR Judicial.xlsx", sheet("summary") firstrow clear
rename source Source
gen type=3
gen stateid=4
* gen electid=stateid*1000+_n
replace type=1 if Year<2002
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
save "Arkansas\AR-Judicial.dta",replace

* COLORADO
import excel "Colorado\CO Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=1
gen stateid=6
* gen electid=stateid*1000+_n
drop Dubois
save "Colorado\CO-Judicial.dta",replace

* FLORIDA
import excel "Florida\FL Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=9
* gen electid=stateid*1000+_n
replace type=1 if Year<1972
save "Florida\FL-Judicial.dta",replace

* GEORGIA
import excel "Georgia\GA Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=10
* gen electid=stateid*1000+_n
replace type=1 if Year<1984
save "Georgia\GA-Judicial.dta",replace

* IDAHO
import excel "Idaho\ID Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=12
* gen electid=stateid*1000+_n
save "Idaho\ID-Judicial.dta",replace

* IOWA
import excel "Iowa\IA Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=1
gen stateid=15
* gen electid=stateid*1000+_n
save "Iowa\IA-Judicial.dta",replace

* ILLINOIS
import excel "Illinois\IL Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
tostring District,replace
drop if Uncontested=="R-W"
gen type=1
gen stateid=13
* gen electid=stateid*1000+_n
save "Illinois\IL-Judicial.dta",replace


* INDIANA
import excel "Indiana\IN Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
drop if Incumbent=="dubois extra"
gen type=1
gen stateid=14
* gen electid=stateid*1000+_n
save "Indiana\IN-Judicial.dta",replace

* KENTUCKY
import excel "Kentucky\KY Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
tostring District,replace
gen type=3
gen stateid=17
* gen electid=stateid*1000+_n
replace type=1 if Year<1976
save "Kentucky\KY-Judicial.dta",replace

* KANSAS
import excel "Kansas\KS Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=1
gen stateid=16
*gen electid=stateid*1000+_n
save "Kansas\KS-Judicial.dta",replace

* LOUISIANA
import excel "Louisiana\LA Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
tostring District,replace
gen type=1
gen stateid=18
* gen electid=stateid*1000+_n
save "Louisiana\LA-Judicial.dta",replace

* MARYLAND
import excel "Maryland\MD Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
tostring District,replace
gen type=2
gen stateid=20
* gen electid=stateid*1000+_n
save "Maryland\MD-Judicial.dta",replace

* MICHIGAN
import excel "Michigan\MI Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=2
gen stateid=22
*gen electid=stateid*1000+_n
save "Michigan\MI-Judicial.dta",replace

* MINNESOTA
import excel "Minnesota\MN Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=23
* gen electid=stateid*1000+_n
save "Minnesota\MN-Judicial.dta",replace

* MISSISSIPPI
import excel "Mississippi\MS Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
tostring District,replace
gen stateid=24
*gen electid=stateid*1000+_n
replace type=1 if Year<1994
save "Mississippi\MS-Judicial.dta",replace

* MONTANA
* NOTE: Uncontested elections with incumbents are treated as RETENTION elections
*       and are coded "R" in "uncontested"
import excel "Montana\MT Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=3
gen stateid=26
* gen electid=stateid*1000+_n
save "Montana\MT-Judicial.dta",replace

* NEBRASKA
import excel "Nebraska\NE Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=27
* gen electid=stateid*1000+_n
save "Nebraska\NE-Judicial.dta",replace

* NEVADA
import excel "Nevada\NV Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=28
* gen electid=stateid*1000+_n
save "Nevada\NV-Judicial.dta",replace

* NORTH CAROLINA
import excel "NorthCarolina\NC Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=1
gen stateid=33
* gen electid=stateid*1000+_n
replace type=3 if inrange(Year,2004,2016)
save "NorthCarolina\NC-Judicial.dta",replace

* NORTH DAKOTA
import excel "NorthDakota\ND Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=3
gen stateid=34
* gen electid=stateid*1000+_n
save "NorthDakota\ND-Judicial.dta",replace

* NEW MEXICO
import excel "New Mexico\NM Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
drop if Uncontested=="R"
gen type=1
gen stateid=31
* gen electid=stateid*1000+_n
save "New Mexico\NM-Judicial.dta",replace

* NEW YORK
*  NOTE:  Correlations include third party votes
import excel "New York\NY Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=1
gen stateid=32
* gen electid=stateid*1000+_n
save "New York\NY-Judicial.dta",replace

* OHIO
import excel "Ohio\OH Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=2
replace type=1 if Year>2021
gen stateid=35
* gen electid=stateid*1000+_n
save "Ohio\OH-Judicial.dta",replace

* OKLAHOMA
import excel "Oklahoma\OK Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=1
gen stateid=36
* gen electid=stateid*1000+_n
save "Oklahoma\OK-Judicial.dta",replace

* OREGON
import excel "Oregon\OR Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=37
* gen electid=stateid*1000+_n
save "Oregon\OR-Judicial.dta",replace

* PENNSYLVANIA
import excel "Pennsylvania\PA Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
drop if Uncontested=="R-W"
drop if Uncontested=="R-L"
gen type=1
gen stateid=38
* gen electid=stateid*1000+_n
save "Pennsylvania\PA-Judicial.dta",replace

* SOUTH DAKOTA
import excel "SouthDakota\SD Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
tostring District,replace
gen type=3
gen stateid=41
* gen electid=stateid*1000+_n 
save "SouthDakota\SD-Judicial.dta",replace

* TENNESSEE
import excel "Tennessee\TN Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=1
gen stateid=42
* gen electid=stateid*1000+_n
save "Tennessee\TN-Judicial.dta",replace

* TEXAS
import excel "Texas\TX Judicial.xlsx", sheet("summary") firstrow clear
strrec Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=1
gen stateid=43
* gen electid=stateid*1000+_n
save "Texas\TX-Judicial.dta",replace

* UTAH
* NOTE: Uncontested elections with incumbents are treated as RETENTION elections
*       and are coded "R" in "uncontested"
import excel "Utah\UT Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=3
gen stateid=44
* gen electid=stateid*1000+_n
replace type=1 if Year<1952
save "Utah\UT-Judicial.dta",replace

* WASHINGTON
import excel "Washington\WA Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=47
* gen electid=stateid*1000+_n
save "Washington\WA-Judicial.dta",replace

* WEST VIRGINIA
import excel "West Virginia\WV Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
drop Dubois
gen type=1
replace type=3 if Year>2015
gen stateid=48
* gen electid=stateid*1000+_n
save "West Virginia\WV-Judicial.dta",replace

* WISCONSIN
import excel "Wisconsin\WI Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=49
* gen electid=stateid*1000+_n
save "Wisconsin\WI-Judicial.dta",replace

* WYOMING
import excel "Wyoming\WY Judicial.xlsx", sheet("summary") firstrow clear
strrec District Uncontested Primary Source Winner Losers ("BLANK" "blank"=""),replace
gen type=3
gen stateid=50
* gen electid=stateid*1000+_n
save "Wyoming\WY-Judicial.dta",replace


* MERGE THE STATES
set more off
cd "D:\Research\Judicial Elections\"
use "Alabama\AL-Judicial.dta", clear
   append using "Arizona\AZ-Judicial.dta"
   append using "Arkansas\AR-Judicial.dta"
   append using "Colorado\CO-Judicial.dta"
   append using "Florida\FL-Judicial.dta"
   append using "Georgia\GA-Judicial.dta"
   append using "Idaho\ID-Judicial.dta"
   append using "Iowa\IA-Judicial.dta"
   append using "Illinois\IL-Judicial.dta"
   append using "Indiana\IN-Judicial.dta"
   append using "Kentucky\KY-Judicial.dta"
   append using "Kansas\KS-Judicial.dta"
   append using "Louisiana\LA-Judicial.dta"
   append using "Maryland\MD-Judicial.dta"
   append using "Michigan\MI-Judicial.dta"
   append using "Minnesota\MN-Judicial.dta"
   append using "Mississippi\MS-Judicial.dta"
   append using "Montana\MT-Judicial.dta"
   append using "Nebraska\NE-Judicial.dta"
   append using "Nevada\NV-Judicial.dta"
   append using "NorthCarolina\NC-Judicial.dta"
   append using "NorthDakota\ND-Judicial.dta"
   append using "New Mexico\NM-Judicial.dta"
   append using "New York\NY-Judicial.dta"
   append using "Ohio\OH-Judicial.dta"
   append using "Oklahoma\OK-Judicial.dta"
   append using "Oregon\OR-Judicial.dta"
   append using "Pennsylvania\PA-Judicial.dta"
   append using "SouthDakota\SD-Judicial.dta"
   append using "Tennessee\TN-Judicial.dta"
   append using "Texas\TX-Judicial.dta"
   append using "Utah\UT-Judicial.dta"
   append using "Washington\WA-Judicial.dta"
   append using "West Virginia\WV-Judicial.dta"
   append using "Wisconsin\WI-Judicial.dta"
   append using "Wyoming\WY-Judicial.dta" 

set more off
tab stateid if Year>2013
list State if Year>2013
   
replace VotePrimary=. if VotePrimary==-9
list State Year if Correlation==-9
replace Correlation=. if Correlation==-9
summ VotePrimary VoteRunOff VoteGeneral Correlation
label define ElectionType 1 "partisan" 2 "mixed" 3 "nonpartisan" 4 "retention"
label val type ElectionType
tab type
tab State
list Year if State==""
drop if Year==. & State==""
rename Year year
rename State state
rename Incumbent incumbent
rename Uncontested uncontested
rename Correlation correlation
rename electid seatid

* generate a two candidate percentage from the Vote Ratio
gen PctPrimary=100*VotePrimary/(1+VotePrimary)
gen PctRunoff=100*VoteRunOff/(1+VoteRunOff)
gen PctGeneral=100*VoteGeneral/(1+VoteGeneral)

label def stateid ///
 1 "AL" 4 "AR" 3 "AZ" 6 "CO" 9 "FL" 10 "GA" 15 "IA" 12 "ID" 13 "IL" ///
 14 "IN" 16 "KS" 17 "KY" 18 "LA" 20 "MD" 22 "MI" 23 "MN" 24 "MS" 26 "MT" ///
 27 "NB" 33 "NC" 34 "ND" 31 "NM" 28 "NV" 32 "NY" 35 "OH" 36 "OK" 37 "OR" ///
 38 "PA" 41 "SD" 42 "TN" 43 "TX" 44 "UT" 47 "WA" 49 "WI" 48 "WV" 50 "WY"
label val stateid stateid
drop T
sort seatid

replace Notes="election determined at primary, no general election" if Primary=="@P"

drop U

save "MergeStates.dta",replace

* write out spreadsheet version

use "MergeStates.dta",clear

* cleanup Primary variable
replace Primary="DP&RP" if inlist(Primary,"DP+RP","DP/RP","DP-RP","DP & RP")
replace Primary="DP-RO&RP" if Primary=="DP-RO/RP"
replace Primary="DP-RO&RP-RO" if Primary=="DP-RO/RP-RO"
replace Primary="DP&RP-RO" if Primary=="DP/RP-RO"
replace Primary="G-RO" if Primary=="GRO"
replace Primary="DP-RO&RP" if Primary=="DP-RO & RP"
replace Primary="OP" if inlist(Primary,"OP-B","OP-D")
replace Primary="OP-RO" if Primary=="G-RO"&state=="LA"
tab Primary

drop VotevsIndependent Winnerpercent Winner Losers Source WinnerVote LoserVote
order seatid year state stateid District type Court Candidates-correlation ///
   VotePrimary-VoteGeneral PctPrimary-PctGeneral Notes
   
rename type BallotFormat

rename Court CourtType

export excel using "Archive\SeatsDataset.xlsx", ///
   sheet("All") firstrow(variables) nolabel replace
